/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package registros;

import java.sql.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

/**
 *
 * @author javier
 */
public class regNaturalAction extends org.apache.struts.action.Action {

	/* forward name="success" path="" */
	private static final String SUCCESS = "success";

	/**
	 * This is the action called from the Struts framework.
	 * @param mapping The ActionMapping used to select this instance.
	 * @param form The optional ActionForm bean for this request.
	 * @param request The HTTP Request we are processing.
	 * @param response The HTTP Response we are processing.
	 * @throws java.lang.Exception
	 * @return
	 */
	@Override
	public ActionForward execute(ActionMapping mapping, ActionForm form,
		HttpServletRequest request, HttpServletResponse response)
		throws Exception {

		regNaturalForm formBean = (regNaturalForm) form;
		String nombre = formBean.getNombre();
		String apellido = formBean.getApellido();
		String telefono = formBean.getTelefono();
		String ci = formBean.getCi();
		String sexo = formBean.getSexo();
		String experiencia = formBean.getExperiencia();
		String estudio = formBean.getEstudio();
		String fecha_nac = formBean.getFecha_nac();
		String municipio = formBean.getMunicipio();
		String estado = formBean.getEstado();
		String parroquia = formBean.getParroquia();
		String detalles = formBean.getDetalles();
		String password = formBean.getPassword();
		String condSocial = formBean.getCondSocial();
		String email = formBean.getEmail();
		String username = formBean.getUsername();
		String profesion = formBean.getProfesion();
		String lugarT = formBean.getLugarT();

		String url = "jdbc:postgresql://localhost/upas_db";
		try {
			Class.forName("org.postgresql.Driver");
			Connection con = DriverManager.getConnection(url, "upas", "upasadmin");
			Statement state = con.createStatement();

			// Agregar usuario en la tabla, se crea un id
			String insertarU = "INSERT INTO usuario VALUES"
				+ " (default,'"+username+"','" + email + "','" + password + "','4');";
			state.executeUpdate(insertarU);
			// Buscamos el id que acabamos de agregar en usuario	

			String selectId = "SELECT id FROM usuario WHERE email='" + email + "';";

			ResultSet Sid = state.executeQuery(selectId);

			Sid.next();

			String id = Sid.getString("id");

			// Agregamos en la tabla ordinario
			String insertarOrd = "INSERT INTO ordinario VALUES ('" + id + "','" + parroquia + "','" + estado + "','" + municipio + "','" + detalles + "')";
			state.executeUpdate(insertarOrd);

			// Calulamos la edad

			String CalcEdad = "SELECT EXTRACT(year from AGE(NOW(),'" + fecha_nac + "')) as age;";

			ResultSet Sedad = state.executeQuery(CalcEdad);
			Sedad.next();


			String edad = Sedad.getString("age");

			// Agregamos en la tabla person natural
			String insertarNat = "INSERT INTO persona_natural values ('" + id + "','" + ci + "','" + nombre + "','" + apellido + "','" + fecha_nac + "','" + sexo + "',NULL,'" + edad + "','" + estudio + "','" + experiencia + "','" + condSocial + "');";
			state.executeUpdate(insertarNat);
		} catch (Exception e) {

			System.out.println(e.getMessage());

			e.printStackTrace();

		}


		return mapping.findForward(SUCCESS);
	}
}
